package grouplife::Schema::Result::TuanZhuanYiComplex;
use strict;
use warnings;
use base qw/DBIx::Class::Core/;

__PACKAGE__->table_class('DBIx::Class::ResultSource::View');

# For the time being this is necessary even for virtual views
__PACKAGE__->table('tuan_zhuanyi_request_complex');
__PACKAGE__->add_columns(
  "id" =>  
  { data_type => "integer", is_nullable => 1},
  "shangyou_user_name" =>
  { data_type => "varchar", is_nullable => 1 },
  "xiayou_user_name" =>
  { data_type => "varchar", is_nullable => 1 },
  "shangyou_tuan_name" =>
  { data_type => "varchar", is_nullable => 1 },
  "zhuanyi_university" =>
  { data_type => "varchar", is_nullable => 1, size => 256 },
  "req_status" =>
  { data_type => "varchar", is_nullable => 1, size => 256 },
  "tuan_image" =>  
  { data_type => "varchar", is_nullable => 1},
  "shangyou_user_id" =>  
  { data_type => "integer", is_nullable => 1},
  "xiayou_user_id" =>  
  { data_type => "integer", is_nullable => 1},
  "shangyou_tuan_id" =>  
  { data_type => "integer", is_nullable => 1},
  "xiayou_tuan_id" =>  
  { data_type => "integer", is_nullable => 1},
  "requester_id" =>  
  { data_type => "integer", is_nullable => 1},
  "requester_name" =>  
  { data_type => "varchar", is_nullable => 1 },
  "zhuanyi_univ_id" =>  
  { data_type => "integer", is_nullable => 1},
  "xiayou_percentage" =>  
  { data_type => "integer", is_nullable => 1},
  "req_detail" =>  
  { data_type => "varchar", is_nullable => 1},
  "created" =>  
  { data_type => "datetime", is_nullable => 1},
  "updated" =>  
  { data_type => "datetime", is_nullable => 1}

);

#
# ->add_columns, etc.
#

# do not attempt to deploy() this view
__PACKAGE__->result_source_instance->is_virtual(1);

__PACKAGE__->result_source_instance->view_definition(q[
  SELECT u1.username as shangyou_user_name,u2.username as xiayou_user_name,u3.username as requester_name,
	t1.name as shangyou_tuan_name, u.name as zhuanyi_university, t1.name as tuan_name, t1.status as tuan_status,
	tzr.status as req_status,t1.image as tuan_image,tzr.*
  FROM tuan_zhuanyi_request tzr 
  JOIN users u1 ON u1.id = tzr.shangyou_user_id 
  JOIN users u2 ON u2.id = tzr.xiayou_user_id
  JOIN users u3 ON u3.id = tzr.requester_id
  JOIN tuan t1 ON t1.id = tzr.shangyou_tuan_id
  JOIN university u ON u.id = tzr.zhuanyi_univ_id
  WHERE tzr.shangyou_user_id = ? OR tzr.xiayou_user_id = ? OR tzr.id = ?
]);
